with shipment as      --任务号信息
         (
             select
                 shipment_no    --任务编号
                  ,shipment_name    --任务名称
                  ,start_code as start_code    --始发中心编号
                  ,start_name as start_name    --始发中心名称
                  ,end_code as end_code    --目的中心编号
                  ,end_name as end_name    --目的中心名称
                  ,business_attribute    --业务属性 1:正班 2:加班
                  ,planned_departure_time    --规划发车时间
                  ,planned_arrival_time    --规划到车时间
                  ,actual_departure_time    --实际发车时间
                  ,actual_arrival_time    --实际到车时间
                  ,vehicletype_name    --车型
                  ,shipment_state    --任务状态,待调度0 已调度1 待执行2 在途3 已完成4 已作废5
                  ,table_type as is_trunk_branch --是否是支干线
                  ,substr(dt,1,10) dt
             from jms_dwd.dwd_tmsnew_shipment_union_base_dt
             where dt between date_add('{{ execution_date | cst_ds }}', -7) and  '{{ execution_date | cst_ds }}'
               and shipment_state<>5
         )
        ,shipment_stop as ( --任务经停信息 A-B-C: A-B,A-C,B-C
    -- 经停表自关联，取出AB，AC，BC线路
    select
        t1.shipment_no
         ,t1.sort_num as start_sort_num
         ,t2.sort_num as end_sort_num
         ,t1.network_code as start_code
         ,t1.network_name as start_name
         ,t2.network_code as end_code
         ,t2.network_name as end_name
         ,t1.planned_departure_time as start_planned_departure_time
         ,t1.actual_departure_time as start_actual_departure_time
         ,t1.planned_arrival_time as start_planned_arrival_time
         ,t1.actual_arrival_time as start_arr_arrival_time
         ,t1.estimate_arrival_time as start_est_arr_time
         ,t2.planned_departure_time as end_planned_departure_time
         ,t2.actual_departure_time as end_actual_departure_time
         ,t2.planned_arrival_time as end_planned_arrival_time
         ,t2.actual_arrival_time as end_actual_arrival_time
         ,t2.estimate_arrival_time as end_est_arr_time
    from (
             select
                 shipment_no
                  ,sort_num
                  ,network_code
                  ,network_name
                  ,planned_departure_time
                  ,actual_departure_time
                  ,planned_arrival_time
                  ,actual_arrival_time
                  ,estimate_arrival_time
             from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt
             where substr(dt,1,10) between date_add('{{ execution_date | cst_ds }}', -7) and  '{{ execution_date | cst_ds }}'
         ) t1 left join
         (
             select
                 shipment_no
                  ,sort_num
                  ,network_code
                  ,network_name
                  ,planned_departure_time
                  ,actual_departure_time
                  ,planned_arrival_time
                  ,actual_arrival_time
                  ,estimate_arrival_time
             from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt
             where substr(dt,1,10) between date_add('{{ execution_date | cst_ds }}', -7) and  '{{ execution_date | cst_ds }}'
         ) t2
         on t1.shipment_no = t2.shipment_no where t1.sort_num < t2.sort_num
)
        ,
     loading as ( --装车扫描去重
         select * from
             (
                 select
                     waybill_no
                      ,shipment_no
                      ,package_code
                      ,scan_site_code as send_code
                      ,scan_site as send_name
                      ,scan_time as load_scan_time
                      ,next_station_code as arr_code
                      ,next_station as arr_name
                      ,null as unload_scan_time
                      ,'装车发件扫描' as scan_type
                      ,scan_time
                      ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time) as row_id
                 from jms_dwd.dwd_tab_barscan_loading_base_dt
                 where substr(dt,1,10) between date_add('{{ execution_date | cst_ds }}', -10) and  date_add('{{ execution_date | cst_ds }}',3) and substr(waybill_no,1,1)!='B'
             )t where row_id=1
     ),
     unloading as (--卸车扫描去重
         select * from
             (
                 select
                     waybill_no
                      ,shipment_no
                      ,package_code
                      ,pre_station_code  as send_code
                      ,pre_station as send_name
                      ,null as load_scan_time
                      ,scan_site_code as arr_code
                      ,scan_site as arr_name
                      ,scan_time as unload_scan_time
                      ,'卸车到件扫描' as scan_type
                      ,scan_time
                      ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time) as row_id
                 from jms_dwd.dwd_tab_barscan_unloading_base_dt
                 where substr(dt,1,10) between date_add('{{ execution_date | cst_ds }}', -7) and  date_add('{{ execution_date | cst_ds }}',5)
                   and substr(waybill_no,1,1)!='B'
             )t where row_id=1
     ),
     shipment_stop_load as ( --经停信息关联装车单号（装车发件本站和下一站关联任务号发车站点和到车站点）
         SELECT /*BROADCAST(t1)*/
             t1.shipment_no
              ,t1.start_sort_num as send_sort
              ,t1.end_sort_num as arr_sort
              ,t1.start_code as send_code
              ,t1.start_name as send_name
              ,t1.end_code as arr_code
              ,t1.end_name as arr_name
              ,t1.start_planned_departure_time as  send_plan_dep_time  --发车站点的计划发车时间
              ,t1.start_actual_departure_time as  send_act_dep_time ---发车站点的实际发车时间
              ,t1.end_planned_arrival_time as  arr_plan_arr_time --到车站点的计划到车时间
              ,t1.end_actual_arrival_time as  arr_act_arr_time  --到车站点的世家到车时间
              ,t1.end_est_arr_time as  arr_est_arr_time  --到车站点的到车时间
              ,t2.waybill_no   --单号
              ,t2.load_scan_time  --发车站点装车发件时间
              ,null as unload_scan_time
         from
             shipment_stop t1
                 inner join loading t2
                            on t1.start_code=t2.send_code and t1.shipment_no=t2.shipment_no and t1.end_code=t2.arr_code
     ),

     shipment_stop_unload as (--经停信息关联装车单号（卸车本站 关联 任务号到车站点）
         SELECT /*BROADCAST(t1)*/
             t1.shipment_no
              ,t1.start_sort_num as send_sort
              ,t1.end_sort_num as arr_sort
              ,t1.start_code as send_code
              ,t1.start_name as send_name
              ,t1.end_code as arr_code
              ,t1.end_name as arr_name
              ,t1.start_planned_departure_time as  send_plan_dep_time  --发车站点的计划发车时间
              ,t1.start_actual_departure_time as  send_act_dep_time ---发车站点的实际发车时间
              ,t1.end_planned_arrival_time as  arr_plan_arr_time --到车站点的计划到车时间
              ,t1.end_actual_arrival_time as  arr_act_arr_time  --到车站点的世家到车时间
              ,t1.end_est_arr_time as  arr_est_arr_time  --到车站点的到车时间
              ,t2.waybill_no   --单号
              ,null as load_scan_time  --发车站点装车发件时间
              ,t2.unload_scan_time
         from
             shipment_stop t1
                 inner join unloading t2
                            on t1.end_code=t2.arr_code and t1.shipment_no=t2.shipment_no
     ),

     stop_load_unload as(  --装车和卸车单号明细合并
         select
             shipment_no,
             max(send_sort) send_sort,
             max(arr_sort) arr_sort,
             send_code,
             max(send_name) send_name,
             arr_code,
             max(arr_name) arr_name,
             max(send_plan_dep_time) as send_plan_dep_time,
             max(send_act_dep_time) as send_act_dep_time,
             max(arr_plan_arr_time) as arr_plan_arr_time,
             max(arr_act_arr_time) as arr_act_arr_time,
             max(arr_est_arr_time) as arr_est_arr_time,
             waybill_no,
             max(load_scan_time) load_scan_time,
             max(unload_scan_time) unload_scan_time
         FROM(
                 select * from shipment_stop_load
                 union all
                 select * from shipment_stop_unload
             )t
         group by shipment_no,send_sort,arr_sort,send_code,arr_code,waybill_no
     ),
     shipment_load_unload as ( --经停单号信息关联任务号信息
         select
             t2.shipment_no    --任务编号
              ,t2.shipment_name    --任务名称
              ,t2.start_code as shipment_start_code    --始发中心编号
              ,t2.start_name as shipment_start_name    --始发中心名称
              ,t2.end_code as shipment_end_code    --目的中心编号
              ,t2.end_name as shipment_end_name    --目的中心名称
              ,t2.business_attribute     --业务属性 1:正班 2:加班
              ,t2.planned_departure_time as ship_plan_dep_time   --规划发车时间
              ,t2.planned_arrival_time as  ship_plan_arr_time  --规划到车时间
              ,t2.actual_departure_time as  ship_act_plan_time  --实际发车时间
              ,t2.actual_arrival_time  as  ship_act_arr_time --实际到车时间
              ,t2.vehicletype_name    --车型
              ,t2.shipment_state    --任务状态,待调度0 已调度1 待执行2 在途3 已完成4 已作废5
              ,t1.send_sort
              ,t1.arr_sort
              ,t1.send_code
              ,t1.send_name
              ,t1.arr_code
              ,t1.arr_name
              ,t1.send_plan_dep_time
              ,t1.send_act_dep_time
              ,t1.arr_plan_arr_time
              ,t1.arr_act_arr_time
              ,t1.arr_est_arr_time
              ,t1.waybill_no
              ,t1.load_scan_time
              ,t1.unload_scan_time
              ,t2.is_trunk_branch
              ,t2.dt
              ,row_number() over(partition by t2.shipment_no,t1.waybill_no,t1.unload_scan_time order by load_scan_time desc) rn
         from stop_load_unload t1
                  inner join  shipment t2 on t1.shipment_no=t2.shipment_no
     )
--操作中有去重：卸车单号关联条件是任务号 到车站点，遇到 AC BC 时 单号卸车会AC BC线路都存在，其中有一条装车发件扫描时间是null
--解决方式：根据单号装车发件时间排序取装车发件第一条
insert overwrite table jms_dm.dm_shipment_stop_billcode_detail_dt partition(dt)
select
    shipment_no            --任务编号
     ,shipment_name          --任务名称
     ,shipment_start_code    --始发中心编号
     ,shipment_start_name    --始发中心名称
     ,shipment_end_code      --目的中心编号
     ,shipment_end_name      --目的中心名称
     ,business_attribute     --业务属性 1:正班 2:加班
     ,ship_plan_dep_time     --规划发车时间
     ,ship_plan_arr_time     --规划到车时间
     ,ship_act_plan_time     --实际发车时间
     ,ship_act_arr_time      --实际到车时间
     ,vehicletype_name       --车型
     ,shipment_state         --任务状态,待调度0 已调度1 待执行2 在途3 已完成4 已作废5
     ,send_sort
     ,arr_sort
     ,send_code as start_site_code
     ,send_name as start_site_name
     ,arr_code as load_next_site_code
     ,arr_name as load_next_site_name
     ,send_plan_dep_time  as planned_departure_time --发车站点的计划发车时间
     ,send_act_dep_time   as actual_departure_time --发车站点的实际发车时间
     ,arr_plan_arr_time   as planned_arrival_time --到车站点的计划到车时间
     ,arr_act_arr_time  as actual_arrival_time --到车站点的到车时间
     ,arr_est_arr_time as estimate_arrival_time --到车站点的到车时间
     ,waybill_no as billcode  --单号
     ,load_scan_time  --发车站点装车发件时间
     ,unload_scan_time  --到车站点卸车到站时间
     ,is_trunk_branch
     ,dt
from shipment_load_unload
where rn=1
distribute by dt,pmod(hash(concat(shipment_no,billcode)),200);